﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//请先添加引用

namespace Usas.DAL.Cms
{
    
   public class UsasDB
    {
       
      
       public DataSet GetEmpDepTrees()
       {
           
           string sql = @"select 
                                'D'+convert(varchar(100),departmentid) as id,
                                departmentname as name,
                                'D'+convert(varchar(100),ParentDepartId) as pid  
                            from 
                                department where del=0
                            union
                            select 
                                convert(varchar(100),employeeid) as id,
                                empname as name,
                                'D'+convert(varchar(100),departmentid) as pid 
                            from 
                                employees 
                            where empstatus=1";

           return DbHelperSQLUsas.Query(sql);
       }

       public DataSet GetRoleDepTrees(string where)
       {
           DbHelperSQL.connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

           string sql = @"select 
                                convert(varchar(100),GROUPID) as id,
                                GROUPNAME as name,
                                '0' as pid  
                            from 
                                GROUPS where GClass = 2
                            union
                            select 
                                convert(varchar(100),users.userid) as id,
 				username as name,
				GROUPID as pid
                            from users
			inner join USERGROUPS on (users.USERID=USERGROUPS.USERID) where " + where;
           return DbHelperSQLUsas.Query(sql);
       }


       public DataSet GetRoleDepEmpInfo(string dpt,string role,string emp)
       {
           DbHelperSQL.connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

           string sql = @"  select 
                            convert(varchar(100),GROUPID) as id,
                            GROUPNAME as name
                            from 
                                GROUPS 
                            where " + role;
           sql += " union select convert(varchar(100),DepartmentId) as id, DepartmentName as name from Department where " + dpt + " and Del=0";
           sql += " union select convert(varchar(100),EmployeeId) as id, EmpName as name from Employees where " + emp + " and EmpStatus=1";
           return DbHelperSQLUsas.Query(sql);
       }

       public DataSet GetRoleDepEmpInfoByTag(string dpt, string role, string emp)
       {
           DbHelperSQL.connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

           string sql = @"  select 
                            convert(varchar(100),GROUPID) as id,
                            GROUPNAME as name
                            from 
                                GROUPS 
                            where " + role;
           sql += " union select 'D' + convert(varchar(100),DepartmentId) as id, DepartmentName as name from Department where " + dpt + " and Del=0";
           sql += " union select convert(varchar(100),EmployeeId) as id, EmpName as name from Employees where " + emp + " and EmpStatus=1";
           return DbHelperSQLUsas.Query(sql);
       }

       public DataSet SelEmployees(string where)
       {
           string SQL = "select Employees.EmployeeId,Employees.Empname,Department.DepartmentId,Department.DepartmentName,Employees.EMAIL from Employees" +
            " left join Department on (Employees.DepartmentId=Department.DepartmentId)" +
            " where Employees.EmpStatus=1 and " + where;
           return DbHelperSQLUsas.Query(SQL);
       }
    }

   
  
}
